package com.gitee.baqkft.code.db;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 数据库工具集
 * @author chenmingcai
 * @date 2017年11月7日 下午4:30:40 
 *
 */

public class DBUtils
{
	protected static final Logger logger = LoggerFactory.getLogger(DBUtils.class);
	
	/**
	 * 获取数据库所有表结构
	 * @param conn	数据库连接
	 * @param dbName	数据库名称
	 * @return	表结构列表
	 * @throws SQLException 
	 */
	public static List<DBTable> getTables(Connection conn) throws SQLException
	{
		List<DBTable> tables = new ArrayList<DBTable>();
		DatabaseMetaData meta = conn.getMetaData();
		ResultSet rs = meta.getTables(conn.getCatalog(), conn.getSchema(), null, new String[]{"TABLE"});
		while(rs.next())
		{
			DBTable table = new DBTable();
			table.setName(rs.getString("TABLE_NAME"));
			table.setComment(rs.getString("REMARKS"));
			tables.add(table);
		}
		
		//获取表的数据列
		for(DBTable table : tables)
		{
			table.setColumns(getColumns(conn, table.getName()));
		}
		
		return tables;
	}
	
	/**
	 * 获取数据表的所有列
	 * @param conn	数据库连接
	 * @param tableName	数据表名称
	 * @return	数据列列表
	 * @throws SQLException 
	 */
	public static List<DBColumn> getColumns(Connection conn, String tableName) throws SQLException
	{
		List<DBColumn> columns = new ArrayList<DBColumn>();
		DatabaseMetaData meta = conn.getMetaData();
		
		//获取主键
		Set<String> primaryKeys = new HashSet<String>();
		ResultSet rs = meta.getPrimaryKeys(conn.getCatalog(), conn.getSchema(), tableName);
		while(rs.next())
		{
			primaryKeys.add(rs.getString("COLUMN_NAME"));
		}
		rs.close();
		
		rs = meta.getColumns(conn.getCatalog(), conn.getSchema(), tableName, null);
		while(rs.next())
		{
			DBColumn column = new DBColumn();
			column.setName(rs.getString("COLUMN_NAME"));
			column.setComment(rs.getString("REMARKS"));
			column.setLength(rs.getInt("COLUMN_SIZE"));
			column.setDefaultValue(rs.getString("COLUMN_DEF"));
			column.setType(rs.getString("TYPE_NAME"));
			column.setPrecision(rs.getInt("DECIMAL_DIGITS"));
			if(primaryKeys.contains(column.getName()))
			{
				column.setPrimary(true);
			}
			else
			{
				column.setPrimary(false);
			}
			columns.add(column);
		}
		return columns;
	}
	
	/**
	 * 关闭数据库连接
	 * @param conn
	 */
	public static void close(Connection conn)
	{
		try
		{
			if(conn != null)
			{
				conn.close();
			}
		}
		catch(SQLException ex)
		{
			
		}
		conn = null;
	}
}
